/*=======================================================================
Creator: Jingyuan Wang, jingyuanwang@u.northwestern.edu
Date created: 				08/07/2018
Date last modified: 		
Purpose: 

Import 2014 MECS raw data


This is a subfunction called in do file 3_1_clean_MECS.do

==========================================================================*/



tempfile MECS
local year = 2014
*********************************
* Import industry code
* and save the first temp file
*********************************
import excel using "$MECSraw/`year'/Table1_1.xlsx", cellrange(A14:B97) sheet("Table 1.1") clear
rename A naics
rename B industry
gen order = _n

* destring the industry code
destring naics, replace
drop if industry == "Subtotal"
replace naics = 99 if industry == "Total"
replace naics = 98 if naics == .
replace industry = "Miscellaneous Nonfuel Products" if naics == 98

* save
save `MECS.dta', replace

*********************************
* Table 1_1
*********************************
local cat = "firstuse"
local use "allpurpose"
forvalues i = 1(1)2 {
	import excel using "$MECSraw/`year'/Table1_`i'.xlsx", cellrange(A8:L97) sheet("Table 1.`i'") clear

	* get variable labels
	foreach var of varlist A-L {
		local l1 = `var'[1]
		local l2 = `var'[2]
		local l3 = `var'[3]
		local l4 = `var'[4]
		if "`l1'" != "" & "`l1'" != " " {
			label var `var' "`l1' `l2' `l3' `l4'"
		}
		else if "`l2'" != "" & "`l2'" != " " {
			label var `var' "`l2' `l3' `l4'"
		}
		else if "`l3'" != "" & "`l3'" != " " {
			label var `var' "`l3' `l4'"
		}
		else {
			label var `var' "`l4'"
		}
	}
	*
	drop if _n <= 6

	* destring variables
	foreach var of varlist C-L {
		count if `var' == "0.5"
		replace `var' = "0.5" if `var' == "*"
		destring `var', replace force
	}
	*
	rename A naics
	rename B industry
	rename C `cat'_`use'_tot_`i'
	rename D `cat'_`use'_elec_`i'
	rename E `cat'_`use'_resdfuel_`i'
	rename F `cat'_`use'_distfuel_`i'
	rename G `cat'_`use'_gas_`i'
	rename H `cat'_`use'_hgl_`i'
	rename I `cat'_`use'_coal_`i'
	rename J `cat'_`use'_coke_`i'
	rename K `cat'_`use'_other_`i'
	rename L `cat'_`use'_shipment_`i'

	* destring the industry code
	destring naics, replace
	drop if industry == "Subtotal"
	replace naics = 99 if industry == "Total"
	capture count if naics == . & substr(industry,1,21) == "Miscellaneous Nonfuel" 
	if `r(N)' == 1 {
		replace naics = 98 if naics == .
		replace industry = "Miscellaneous Nonfuel Products" if naics == 98
	}
	if `r(N)' > 1 {
		disp("PROBLEMATIC!")
	}
	*

	* merge 
	merge 1:1 naics using `MECS.dta'
	drop _merge

	* save
	save `MECS.dta', replace 

}
*


*********************************
* Table 2_1
*********************************
local cat = "firstuse"
local use = "nonfuel"
local lastcol = "J"
forvalues i = 1(1)2 {
	import excel using "$MECSraw/`year'/Table2_`i'.xlsx", cellrange(A8:`lastcol'97) sheet("Table 2.`i'") clear

	* get variable labels
	foreach var of varlist A-`lastcol' {
		local l1 = `var'[1]
		local l2 = `var'[2]
		local l3 = `var'[3]
		local l4 = `var'[4]
		if "`l1'" != "" & "`l1'" != " " {
			label var `var' "`l1' `l2' `l3' `l4'"
		}
		else if "`l2'" != "" & "`l2'" != " " {
			label var `var' "`l2' `l3' `l4'"
		}
		else if "`l3'" != "" & "`l3'" != " " {
			label var `var' "`l3' `l4'"
		}
		else {
			label var `var' "`l4'"
		}
	}
	*
	drop if _n <= 6

	* destring variables
	foreach var of varlist C-`lastcol' {
		count if `var' == "0.5"
		replace `var' = "0.5" if `var' == "*"
		destring `var', replace force
	}
	*
	rename A naics
	rename B industry
	rename C `cat'_`use'_tot_`i'
	rename D `cat'_`use'_resdfuel_`i'
	rename E `cat'_`use'_distfuel_`i'
	rename F `cat'_`use'_gas_`i'
	rename G `cat'_`use'_hgl_`i'
	rename H `cat'_`use'_coal_`i'
	rename I `cat'_`use'_coke_`i'
	rename J `cat'_`use'_other_`i'

	* destring the industry code
	destring naics, replace
	drop if industry == "Subtotal"
	replace naics = 99 if industry == "Total"
	capture count if naics == . & substr(industry,1,21) == "Miscellaneous Nonfuel" 
	if `r(N)' == 1 {
		replace naics = 98 if naics == .
		replace industry = "Miscellaneous Nonfuel Products" if naics == 98
	}
	if `r(N)' > 1 {
		disp("PROBLEMATIC!")
	}
	*
	
	* merge 
	merge 1:1 naics using `MECS.dta'
	drop _merge

	* save
	save `MECS.dta', replace 

}
*



*********************************
* Table 3_1
*********************************
local cat = "firstuse"
local use = "fuel"
local lastcol = "K"
local lastrow = 95
forvalues i = 1(1)2 {
	import excel using "$MECSraw/`year'/Table3_`i'.xlsx", cellrange(A8:`lastcol'`lastrow') sheet("table 3.`i'") clear

	* get variable labels
	foreach var of varlist A-`lastcol' {
		local l1 = `var'[1]
		local l2 = `var'[2]
		local l3 = `var'[3]
		local l4 = `var'[4]
		if "`l1'" != "" & "`l1'" != " " {
			label var `var' "`l1' `l2' `l3' `l4'"
		}
		else if "`l2'" != "" & "`l2'" != " " {
			label var `var' "`l2' `l3' `l4'"
		}
		else if "`l3'" != "" & "`l3'" != " " {
			label var `var' "`l3' `l4'"
		}
		else {
			label var `var' "`l4'"
		}
	}
	*
	drop if _n <= 6

	* destring variables
	foreach var of varlist C-`lastcol' {
		count if `var' == "0.5"
		replace `var' = "0.5" if `var' == "*"
		destring `var', replace force
	}
	*
	rename A naics
	rename B industry
	rename C `cat'_`use'_tot_`i'
	rename D `cat'_`use'_elec_`i'
	rename E `cat'_`use'_resdfuel_`i'
	rename F `cat'_`use'_distfuel_`i'
	rename G `cat'_`use'_gas_`i'
	rename H `cat'_`use'_hgl_`i'
	rename I `cat'_`use'_coal_`i'
	rename J `cat'_`use'_coke_`i'
	rename K `cat'_`use'_other_`i'

	* destring the industry code
	destring naics, replace
	drop if industry == "Subtotal"
	replace naics = 99 if industry == "Total"
	capture count if naics == . & substr(industry,1,21) == "Miscellaneous Nonfuel" 
	if `r(N)' == 1 {
		replace naics = 98 if naics == .
		replace industry = "Miscellaneous Nonfuel Products" if naics == 98
	}
	if `r(N)' > 1 {
		disp("PROBLEMATIC!")
	}
	*
	
	* merge 
	merge 1:1 naics using `MECS.dta'
	drop _merge

	* save
	save `MECS.dta', replace 

}
*


*********************************
* Table 4_1 - 4_2 
*********************************
local cat = "offsiteProd"
local lastcol = "K"
local lastrow = 95
forvalues i = 1(1)2 {
	import excel using "$MECSraw/`year'/Table4_`i'.xlsx", cellrange(A8:`lastcol'`lastrow') sheet("Table 4.`i'") clear

	* get variable labels
	foreach var of varlist A-`lastcol' {
		local l1 = `var'[1]
		local l2 = `var'[2]
		local l3 = `var'[3]
		local l4 = `var'[4]
		if "`l1'" != "" & "`l1'" != " " {
			label var `var' "`l1' `l2' `l3' `l4'"
		}
		else if "`l2'" != "" & "`l2'" != " " {
			label var `var' "`l2' `l3' `l4'"
		}
		else if "`l3'" != "" & "`l3'" != " " {
			label var `var' "`l3' `l4'"
		}
		else {
			label var `var' "`l4'"
		}
	}
	*
	drop if _n <= 6

	* destring variables
	foreach var of varlist C-`lastcol' {
		count if `var' == "0.5"
		replace `var' = "0.5" if `var' == "*"
		destring `var', replace force
	}
	*
	rename A naics
	rename B industry
	rename C `cat'_tot_`i'
	rename D `cat'_elec_`i'
	rename E `cat'_resdfuel_`i'
	rename F `cat'_distfuel_`i'
	rename G `cat'_gas_`i'
	rename H `cat'_hgl_`i'
	rename I `cat'_coal_`i'
	rename J `cat'_coke_`i'
	rename K `cat'_other_`i'

	* destring the industry code
	destring naics, replace
	drop if industry == "Subtotal"
	replace naics = 99 if industry == "Total"
	capture count if naics == . & substr(industry,1,21) == "Miscellaneous Nonfuel" 
	if `r(N)' == 1 {
		replace naics = 98 if naics == .
		replace industry = "Miscellaneous Nonfuel Products" if naics == 98
	}
	if `r(N)' > 1 {
		disp("PROBLEMATIC!")
	}
	*
	
	* merge 
	merge 1:1 naics using `MECS.dta'
	drop _merge

	* save
	save `MECS.dta', replace 

}
*



*********************************
* Table 7_6 & 7_9
*********************************
local cat = "Purchased"
local use = "fuel"
local lastcol = "K"
local lastrow = 96
local firstrow = 7
local name_rows = 8
foreach j of num 6 9 {
	import excel using "$MECSraw/`year'/Table7_`j'.xlsx", cellrange(A`firstrow':`lastcol'`lastrow') sheet("Table 7.`j'") clear

	local i = "quat"
	if `j' == 9 {
		local i = "usd"
	}
	*
	* get variable labels
	foreach var of varlist A-`lastcol' {
		local l1 = `var'[1]
		local l2 = `var'[2]
		local l3 = `var'[3]
		local l4 = `var'[4]
		if "`l1'" != "" & "`l1'" != " " {
			label var `var' "`l1' `l2' `l3' `l4'"
		}
		else if "`l2'" != "" & "`l2'" != " " {
			label var `var' "`l2' `l3' `l4'"
		}
		else if "`l3'" != "" & "`l3'" != " " {
			label var `var' "`l3' `l4'"
		}
		else {
			label var `var' "`l4'"
		}
	}
	*
	drop if _n <= `name_rows'

	* destring variables
	foreach var of varlist C-`lastcol' {
		count if `var' == "0.5"
		replace `var' = "0.5" if `var' == "*"
		destring `var', replace force
	}
	*
	rename A naics
	rename B industry
	rename C `cat'_tot_`i'
	rename D `cat'_elec_`i'
	rename E `cat'_resdfuel_`i'
	rename F `cat'_distfuel_`i'
	rename G `cat'_gas_`i'
	rename H `cat'_hgl_`i'
	rename I `cat'_coal_`i'
	rename J `cat'_coke_`i'
	rename K `cat'_other_`i'

	* destring the industry code
	destring naics, replace
	drop if industry == "Subtotal"
	replace naics = 99 if industry == "Total"
	capture count if naics == . & substr(industry,1,21) == "Miscellaneous Nonfuel" 
	if `r(N)' == 1 {
		replace naics = 98 if naics == .
		replace industry = "Miscellaneous Nonfuel Products" if naics == 98
	}
	if `r(N)' > 1 {
		disp("PROBLEMATIC!")
	}
	*
	
	* merge 
	merge 1:1 naics using `MECS.dta'
	drop _merge

	* save
	save `MECS.dta', replace 

}
*


*********************************
* Table 11_1 
*********************************
local lastcol = "G"
local lastrow = 94
local firstrow = 7
local name_rows = 6
	import excel using "$MECSraw/`year'/Table11_1.xlsx", cellrange(A`firstrow':`lastcol'`lastrow') sheet("Table 11.1") clear

	* get variable labels
	foreach var of varlist A-`lastcol' {
		local l1 = `var'[1]
		local l2 = `var'[2]
		local l3 = `var'[3]
		local l4 = `var'[4]
		if "`l1'" != "" & "`l1'" != " " {
			label var `var' "`l1' `l2' `l3' `l4' (million kWh)"
		}
		else if "`l2'" != "" & "`l2'" != " " {
			label var `var' "`l2' `l3' `l4' (million kWh)"
		}
		else if "`l3'" != "" & "`l3'" != " " {
			label var `var' "`l3' `l4' (million kWh)"
		}
		else {
			label var `var' "`l4' (million kWh)"
		}
	}
	*
	drop if _n <= `name_rows'

	* destring variables
	foreach var of varlist C-`lastcol' {
		count if `var' == "0.5"
		replace `var' = "0.5" if `var' == "*"
		destring `var', replace force
	}
	*
	rename A naics
	rename B industry
	rename C Purchased_elec
	rename D TransferIn_elec
	rename E OnsiteGen_elec
	rename F TransferOff_elec
	rename G NetDemand_elec


	* destring the industry code
	destring naics, replace
	drop if industry == "Subtotal"
	replace naics = 99 if industry == "Total"
	capture count if naics == . & substr(industry,1,21) == "Miscellaneous Nonfuel" 
	if `r(N)' == 1 {
		replace naics = 98 if naics == .
		replace industry = "Miscellaneous Nonfuel Products" if naics == 98
	}
	if `r(N)' > 1 {
		disp("PROBLEMATIC!")
	}
	*
	
	* merge 
	merge 1:1 naics using `MECS.dta'
	drop _merge

	* save
	save `MECS.dta', replace 
	
*********************************
* SAVE
*********************************
* save for this year
gen year = `year'
order order naics industry year
sort order
save "$MECSenergy/MECS`year'.dta", replace



